package larrys.pomodoro.daoimpl;

import static java.sql.Types.BIGINT;
import static java.sql.Types.TINYINT;
import static java.sql.Types.VARCHAR;

import java.util.Collection;
import java.util.List;

import larrys.pomodoro.daoimpl.rowmappers.RoleRowMapper;
import larrys.pomodoro.daoimpl.rowmappers.UserRowMapper;
import larrys.pomodoro.domain.entity.PomodoroUser;
import larrys.pomodoro.domain.entity.Role;
import larrys.pomodoro.domain.repository.UserRepository;
import larrys.pomodoro.util.AssertUtil;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserRepositoryImpl implements UserRepository {

	private static Log logger = LogFactory.getLog(UserRepositoryImpl.class);
	private JdbcTemplate jdbcTemplate;

	@Autowired
	@Required
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private final static String SQL_INSERT_USER_ROLE_MAPPING = "INSERT INTO user_role (user_id, role_id) VALUES(?,?)";

	private void addUserRoleMapping(PomodoroUser user) {
		Collection<Role> roles = user.getRoles();
		for (Role role : roles) {
			jdbcTemplate.update(SQL_INSERT_USER_ROLE_MAPPING, new Object[] {
					user.getId(), role.getId() }, new int[] { BIGINT, BIGINT });
		}
	}

	private final static String SQL_DELETE_USER_ROLE_MAPPING = "DELETE FROM user_role WHERE user_id = ?";

	private void deleteUserRoleMapping(long userId) {
		jdbcTemplate.update(SQL_DELETE_USER_ROLE_MAPPING,
				new Object[] { userId }, new int[] { BIGINT });
	}

	private final static String SQL_QUERY_ROLE_BY_USER_ID = "SELECT r.id, r.role_name FROM user_role ur JOIN role r ON ur.role_id=r.id WHERE ur.user_id = ?";

	private final static String SQL_QUERY_USER_BY_EMAIL = "SELECT id, email, user_name, password, male, expired, locked, credentials_expired, enabled FROM user WHERE email = ?";

	@Override
	public PomodoroUser getUserByEmail(String userEmail) {
		AssertUtil.argumentNotNull(userEmail, "userEmail");
		logger.debug("Trying to get user[email:" + userEmail + "]");
		PomodoroUser user = null;
		try {
			List<PomodoroUser> userList = jdbcTemplate.query(
					SQL_QUERY_USER_BY_EMAIL, new Object[] { userEmail },
					new int[] { java.sql.Types.VARCHAR }, new UserRowMapper());
			if (userList.size() > 0) {
				user = userList.get(0);
				List<Role> roleList = jdbcTemplate.query(
						SQL_QUERY_ROLE_BY_USER_ID,
						new Object[] { user.getId() }, new int[] { BIGINT },
						new RoleRowMapper());
				for (Role role : roleList) {
					user.addRole(role);
				}
			}
		} catch (RuntimeException e) {
			logger.error("Exception threw while getting user[email:"
					+ userEmail + "]", e);
			throw e;
		}
		logger.debug("Returning " + user);
		return user;
	}

	private final static String SQL_QUERY_USER_BY_ID = "SELECT id, email, user_name, password, male, expired, locked, credentials_expired, enabled FROM user WHERE id = ?";

	@Override
	public PomodoroUser getUserById(long id) {
		AssertUtil.validId(id);
		logger.debug("Trying to get user[id:" + id + "]");
		PomodoroUser user = null;
		try {
			List<PomodoroUser> userList = jdbcTemplate.query(
					SQL_QUERY_USER_BY_ID, new Object[] { id },
					new int[] { BIGINT }, new UserRowMapper());
			if (userList.size() > 0) {
				user = userList.get(0);
				List<Role> roleList = jdbcTemplate.query(
						SQL_QUERY_ROLE_BY_USER_ID, new Object[] { id },
						new int[] { BIGINT }, new RoleRowMapper());
				for (Role role : roleList) {
					user.addRole(role);
				}
			}
		} catch (RuntimeException e) {
			logger.error("Exception threw while getting user[id:" + id + "]", e);
			throw e;
		}
		logger.debug("Returning " + user);
		return user;
	}

	private final static String SQL_INSERT_USER = "INSERT INTO user (id, email, user_name, password, male, expired, locked, credentials_expired, enabled, deleted) VALUES(null,?,?,?,?,?,?,?,?,0)";

	@Override
	public PomodoroUser addUser(PomodoroUser newUser) {
		AssertUtil.argumentNotNull(newUser, "newUser");
		AssertUtil.propertyShouldNotBeNull(newUser.getEmail(), "newUser",
				"email");
		AssertUtil.propertyShouldNotBeNull(newUser.getUsername(), "newUser",
				"username");

		logger.debug("Trying to add new " + newUser);
		try {
			jdbcTemplate
					.update(SQL_INSERT_USER,
							new Object[] { newUser.getEmail(),
									newUser.getUsername(),
									newUser.getPassword(), newUser.isMale(),
									newUser.isExpired(), newUser.isLocked(),
									newUser.isCredentialsExpired(),
									newUser.isEnabled() }, new int[] { VARCHAR,
									VARCHAR, VARCHAR, TINYINT, TINYINT,
									TINYINT, TINYINT, TINYINT });
		} catch (RuntimeException e) {
			logger.error("Exception threw while adding " + newUser, e);
			throw e;
		}
		long userId = jdbcTemplate.queryForLong(
				"SELECT id FROM user WHERE email = ?", newUser.getEmail());
		if (userId > 0) {
			logger.info("New " + newUser + " added successfully!");
		} else {
			logger.error("PomodoroUser id did not return correctly while adding user, the bad id returned is:"
					+ userId);
			throw new RuntimeException("BAD user id:" + userId);
		}
		PomodoroUser addedUser = newUser.cloneUserWithIdAndEmail(userId,
				newUser.getEmail());
		try {
			addUserRoleMapping(addedUser);
		} catch (RuntimeException e) {
			logger.error("Exception threw while adding user's role mapping,"
					+ newUser, e);
			throw e;
		}
		return addedUser;
	}

	private final static String SQL_UPDATE_USER = "UPDATE user SET user_name = ?, password = ?, male = ?, expired = ?, locked = ?, credentials_expired = ?, enabled = ? WHERE id = ?";

	@Override
	public void updateUser(PomodoroUser user) {
		AssertUtil.argumentNotNull(user, "user");
		AssertUtil.propertyShouldNotBeNull(user.getEmail(), "user", "email");
		AssertUtil.propertyShouldNotBeNull(user.getUsername(), "user",
				"username");

		logger.debug("Trying to update " + user);
		try {
			jdbcTemplate.update(
					SQL_UPDATE_USER,
					new Object[] { user.getUsername(), user.getPassword(),
							user.isMale(), user.isExpired(), user.isLocked(),
							user.isCredentialsExpired(), user.isEnabled(),
							user.getId() }, new int[] { VARCHAR, VARCHAR,
							BIGINT, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT });
		} catch (RuntimeException e) {
			logger.error("Exception threw while updating " + user, e);
			throw e;
		}
		try {
			deleteUserRoleMapping(user.getId());
		} catch (RuntimeException e) {
			logger.error(
					"Exception threw while deleting user's all role mapping, "
							+ user, e);
			throw e;
		}
		try {
			addUserRoleMapping(user);
		} catch (RuntimeException e) {
			logger.error("Exception threw while update user's role mapping,"
					+ user, e);
			throw e;
		}
		logger.info(user + " updated successfully!");
	}

	private final static String SQL_MARK_USER_DELETED = "UPDATE user SET deleted = 1 WHERE id = ?";

	@Override
	public void markUserDeletedById(long id) {

		logger.debug("Trying to marked deleted PomodoroUser[id:" + id + "]");
		try {
			jdbcTemplate.update(SQL_MARK_USER_DELETED, new Object[] { id },
					new int[] { BIGINT });
		} catch (RuntimeException e) {
			logger.error(
					"Exception threw while marking user deleted, PomodoroUser[id:"
							+ id + "]", e);
			throw e;
		}
		logger.info("PomodoroUser[id:" + id + "] marked deleted successfully!");
	}

}
